package com.yumo.kangchenjunga.settlement;

import java.time.LocalDate;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface StatementEntryRepository extends JpaRepository<StatementEntryEntity, Integer> {

	StatementEntryEntity findFirstByCompanyIdOrderByEndDateDesc(int companyId);

	@Query(nativeQuery = true, value = ""
			+ " select count(1) from ("
			+ " select 1 from statement_entry where company_id = ?1 and start_date >= ?2 and end_date <= ?3"
			+ " union all"
			+ " select 1 from statement_entry where company_id = ?1 and start_date < ?2 and end_date >= ?2"
			+ " union all"
			+ " select 1 from statement_entry where company_id = ?1 and start_date <= ?3 and end_date > ?3) t")
	int countByDate(int companyId, LocalDate startDate, LocalDate endDate);

}
